from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from config import DB_URL
from models.city import engine


def get_weather_statistics(city):
    try:
        with engine.connect() as connection:
            # 执行 SQL 查询
            query = text(f"SELECT WeatherCondition FROM {city}")
            result = connection.execute(query)

            database_table = city
            # 初始化一个空字典用于存储天气类型和对应的次数
            weather_count = {}

            # 遍历查询结果
            for row in result:
                weather = row[0]
                if weather in weather_count:
                    weather_count[weather] += 1
                else:
                    weather_count[weather] = 1

            # 输出表明数据来源的信息
            print(f"以下是来自 {database_table} 数据库表的天气统计结果：")

            # 输出统计结果
            for weather, count in weather_count.items():
                print(f"{weather}: {count} 次")

            return weather_count
    except SQLAlchemyError as err:
        print(f"数据库连接或查询出错: {err}")
        return None
